#!/usr/bin/env python
# -*- coding:utf-8 -*-

# file:app.py
# author:董奥运120231080608
# datetime:2024/12/25 19:53
# software: PyCharm
"""
题目一 Web开发+数据库开发
"""
from flask import Flask, render_template, jsonify, request
import flask
from flask import url_for  # 进行网页跳转
import os  # 用于操作系统文件的依赖库
import pymysql  # 连接数据库
import csv
from io import TextIOWrapper
from io import StringIO
app = flask.Flask(__name__)

# 初始化
app = flask.Flask(__name__)
# 初始化数据库连接
# 使用pymysql.connect方法连接本地mysql数据库
db = pymysql.connect(host='127.0.0.1', port=3306, user='root',
                     password='Ao20060413', database='student', charset='utf8')
# 操作数据库，获取db下的cursor对象
cursor = db.cursor()
sql11="select * from students"
cursor.execute(sql11)
result = cursor.fetchall()
students_list=[]
for i in result:
    students_list.append({'Sname':i[0],'Sno':i[1],'Ssex':i[2],'Sage':i[3],'Dno':i[4],'Sclass':i[5],'address':i[6]})

# 路由
@app.route('/')
def hello_world():
    return render_template('index.html')

@app.route('/login', methods=['POST'])
def login():
    username = request.form.get('username')
    password = request.form.get('password')
    return render_template('admin.html',students_list=students_list)

@app.route('/delete/<name>')
def delete(name):
    for i in students_list:
        if i['Sname'] == name:
            sql_2 = "delete from students where Sname=%s"
            cursor.execute(sql_2, name)
            db.commit()
            students_list.remove(i)
            break
    return render_template('admin.html',students_list=students_list)

@app.route('/change/<name>')
def change(name):
    for i in students_list:
        if i['Sname'] == name:
            return render_template('change.html',user=i)
    return render_template('admin.html',students_list=students_list)

@app.route('/changed/<name>', methods=['POST'])
def changed(name):
    for i in students_list:
        if  i['Sname'] == name:
            i['Sno'] = request.form.get('Sno')
            i['Sname'] = request.form.get('Sname')
            i['Ssex'] = request.form.get('Ssex')
            i['Sage'] = request.form.get('Sage')
            i['Dno'] = request.form.get('Dno')
            i['Sclass'] = request.form.get('Sclass')
            i['address'] = request.form.get('address')
            sql_3 = "update students set Sno=%s,Sname=%s,Ssex=%s,Sage=%s,Dno=%s,Sclass=%s,address=%s where Sname=%s"
            cursor.execute(sql_3,(i['Sno'],i['Sname'],i['Ssex'],i['Sage'],i['Dno'],i['Sclass'],i['address'],name))
            db.commit()
            break
    return render_template('admin.html',students_list=students_list)
@app.route('/add')
def add():
    return render_template('add.html')

@app.route('/add2', methods=['POST'])
def add2():
    i = {}
    i['Sname'] = request.form.get('Sname')
    i['Sno'] = request.form.get('Sno')
    i['Ssex'] = request.form.get('Ssex')
    i['Sage'] = request.form.get('Sage')
    i['Dno'] = request.form.get('Dno')
    i['Sclass'] = request.form.get('Sclass')
    i['address'] = request.form.get('address')
    students_list.insert(0,i)
    try:
        # 信息存入数据库
        sql_1 = "insert into students(Sname,Sno,Ssex,Sage,Dno,Sclass,address)values(%s,%s,%s,%s,%s,%s,%s)"
        cursor.execute(sql_1, (i['Sname'],i['Sno'],i['Ssex'],i['Sage'],i['Dno'],i['Sclass'],i['address']))
        db.commit()
        # result = cursor.fetchone()
        insert_result = "成功存入一条学生信息"
        print(insert_result)
    except Exception as err:
        print(err)
        insert_result = "学生信息插入失败"
        print(insert_result)
        pass
    return render_template('admin.html', students_list=students_list)

if __name__ == '__main__':
    app.run(debug=True)
